import MySQLdb


class MySQLServer(object):

    def __init__(self, host, port, user, password, database, charset):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset

    def create_conn(self):
        self.connect = MySQLdb.connect(host=self.host, port=self.port, user=self.user,
                                       password=self.password, database=self.database,
                                       charset=self.charset)
        if self.connect:
            print('Database connection is successful')
            self.cursor = self.connect.cursor(MySQLdb.cursors.DictCursor)
        else:
            print('Database connection false')

    def server_close(self):
        self.cursor.close()
        self.connect.cursor()

    def add_element(self, eno, ename, job, mgr, sal, comm, dno):
        """添加员工信息"""
        result = self.cursor.execute('insert into tb_emp values (%s,%s,%s,%s,%s,%s,%s)',
                                     (eno, ename, job, mgr, sal, comm, dno))
        if result == 1:
            print('添加信息成功')
        self.connect.commit()

    def delete_element(self, eno):
        """删除员工信息"""
        result = self.cursor.execute('delete from tb_emp where eno=%s', (eno,))
        if result == 1:
            print('删除员工成功')
        self.connect.commit()

    def update_element(self, ename, job, comm, eno):
        """更新员工信息"""
        result = self.cursor.execute(
            'update tb_emp set ename=%s, job=%s, comm=%s where eno=%s',
            (ename, job, comm, eno))
        if result == 1:
            print('更新员工信息成功')
        self.connect.commit()

    def select_element(self):
        """查询员工信息"""
        self.cursor.execute('select * from tb_emp')
        for row in self.cursor.fetchall():
            print(row)


def main():
    # host_i = input('请输入主机IP地址:')
    # port_i = int(input('请输入连接端口：'))
    # user_i = input('请输入用户名：')
    # password_i = input('请输入密码:')
    # database_i = input('请输入数据库的名称:')
    # charset_i = input('请输入字符集编码：')
    emp = MySQLServer(host='121.199.18.215', port=3306, user='new_hrs', password='5201314@Dcs',
                      database='new_hrs',  charset='utf8')
    try:
        emp.create_conn()
        select = int(input('请输入想要进行的操作：1、添加元素\t'
                           '2、删除元素\t'
                           '3、修改元素\t'
                           '4、查询信息\t'
                           '5、退出\t：'))
        if select == 1:
            eno = input('请输入部员工的编号：')
            ename = input('请输入员工的姓名：')
            job = input('请输入员工的职位：')
            mgr = input('请输入员工的主管：')
            sal = input('请输入员工的月薪：')
            comm = input('请输入员工的津贴：')
            dno = input('请输入员工的部门编号：')
            emp.add_element(eno, ename, job, mgr, sal, comm, dno)
        elif select == 2:
            eno = input('请输入需要删除的员工的编号:')
            emp.delete_element(eno)
        elif select == 3:
            eno = input('请输入需要更新部员工的编号：')
            ename = input('请输入需要更新员工的姓名：')
            job = input('请输入需要更新员工的职位：')
            sal = input('请输入需要更新员工的月薪：')
            comm = input('请输入需要更新员工的津贴：')
            emp.update_element(ename, job, sal, comm, eno)
        elif select == 4:
            emp.select_element()
        else:
            print('输入指令有误')
    except MySQLdb.MySQLError as err:
        print(err)
    finally:
        emp.server_close()


if __name__ == "__main__":
    main()
